import pandas as pd
import numpy as np
import os
from typing import List

stockList80=[
'000001.SZ',
'000002.SZ',
'000333.SZ',
'000625.SZ',
'000651.SZ',
'000796.SZ',
'000858.SZ',
'000895.SZ',
'000878.SZ',
'000961.SZ',
'002237.SZ',
'002384.SZ',
'002415.SZ',
'002475.SZ',
'002563.SZ',
'002683.SZ',
'002812.SZ',
'002821.SZ',
'300003.SZ',
'300630.SZ',
'600009.SH',
'600030.SH',
'600036.SH',
'600048.SH',
'600068.SH',
'600276.SH',
'600519.SH',
'600522.SH',
'600547.SH',
'600585.SH',
'600612.SH',
'600690.SH',
'600703.SH',
'600741.SH',
'600754.SH',
'600809.SH',
'600837.SH',
'600887.SH',
'600900.SH',
'601021.SH',
'601166.SH',
'601318.SH',
'601336.SH',
'601398.SH',
'601688.SH',
'601888.SH',
'601933.SH',
'603228.SH',
'603658.SH',
'603833.SH',]



data=pd.read_excel('312持仓20200416.xls')
position=list(data['T日指令可用数量'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['基金名称'])
combi=list(data['组合名称'])
stockholder=list(data['股东代码'])
codeList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
    code=int(stocks[i])
    pos=position[i]
    if (pos<100):
        continue
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
        marketname='深交所A'
        seat='061700'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
        marketname='上交所A'
        seat='33211'
    else:
        continue
    mymv=price[i]*pos
    codeList.append({'code':codeStr,'codeInt':code,'account':acc,'combi':combi[i],'market':marketname,'stockholder':stockholder[i],'seat':seat,'hold':pos,'name':names[i],'marketValue':mymv,'price':price[i]})
stocks312=pd.DataFrame(codeList)
stocks312=stocks312[stocks312['stockholder'].isin(['0899018280','D890188317'])]
#print(stocks312)


data=pd.read_excel('314持仓20200416.xls')
position=list(data['持仓'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['基金名称'])
combi=list(data['组合名称'])
stockholder=list(data['股东代码'])
codeList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
    code=int(stocks[i])
    pos=position[i]
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
        marketname='深交所A'
        seat='061700'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
        marketname='上交所A'
        seat='33211'
    else:
        continue

    codeList.append({'code':codeStr,'codeInt':code,'account':acc,'combi':combi[i],'market':marketname,'stockholder':stockholder[i],'seat':seat,'hold':pos,'name':names[i],'marketValue':mv[i],'price':price[i]})
stocks314=pd.DataFrame(codeList)
#print(stocks314)




data=pd.read_excel('流通股0415.xlsx')
position=list(data['流通量(万股)'])
positionall=list(data['持仓量(万股)'])
marketValue=list(data['最新市值(万元)'])
stocks=list(data['代码'])
names=list(data['证券名称'])
holdList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]*10000
    codeStr=str(code).rjust(6,'0')
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    price=marketValue[i]/positionall[i]
    holdList.append({'code':codeStr,'name':names[i],'hold':pos,'price':price})
stocksTotal=pd.DataFrame(holdList)
#print(stocksTotal)

stocks80=stocksTotal[stocksTotal['code'].isin(stockList80)]
stocks40=stocksTotal[~stocksTotal['code'].isin(stockList80)]
mv80=(stocks80['hold']*0.8*stocks80['price']).sum()
mv40=(stocks40['hold']*0.4*stocks40['price']).sum()
print(mv80)
print(mv40)

####################################################################
#统计低beta的股票划券情况
####################################################################
totalmovemv=0
checkinfo=[]
trytomove=[]
combi312=list(stocks312['combi'].unique())
combi314=list(stocks314['combi'].unique())
combiall=combi312+combi314
stocksall=pd.concat([stocks312,stocks314])
print(combi312)
print(combi314)
moveratio=0.8
t02ratio=0.3
for code in stockList80:
    totalpos=np.round(stocksTotal[stocksTotal['code']==code]['hold'].iloc[0])
    price=stocksTotal[stocksTotal['code']==code]['price'].iloc[0]
    pos312=np.round(stocks312[stocks312['code']==code]['hold'].sum())
    #pos314=np.round(stocks314[stocks314['code']==code]['hold'].sum())+np.round(stocks314007[stocks314007['code']==code]['hold'].sum())
    pos314=np.round(stocks314[stocks314['code']==code]['hold'].sum())
    if (pos312<=100):
        continue
    myname=stocksall[stocksall['code']==code]['name'].iloc[0]
    marketname=stocksall[stocksall['code']==code]['market'].iloc[0]
    mystockholder=stocksall[stocksall['code']==code]['stockholder'].iloc[0]
    myseat=stocksall[stocksall['code']==code]['seat'].iloc[0]
    difference=totalpos-pos314-pos312
    totalmove=np.round(totalpos*moveratio,-2)
    moved=np.round(pos314,-2)
    needtomove=totalmove-moved
    if needtomove<0:
        needtomove=0
    if needtomove>pos312:
        needtomove=pos312
    if (needtomove<1000):
        continue
    movemv=price*needtomove
    totalmovemv=totalmovemv+movemv
    checkinfo.append({'code':code,'name':myname,'price':price,'total':totalpos,'312':pos312,'314':pos314,'difference':difference,'move':needtomove,'moveMarketValue':movemv})
    #print(f"{code} {myname} price:{price} total:{totalpos} 312:{pos312} 314:{pos314} diff:{difference} needtomove:{needtomove} movemv:{movemv}")
    mydict={}
    for combi in combiall:
        stocksSelect=stocksall[(stocksall['code']==code) & (stocksall['combi']==combi)]
        if (stocksSelect.shape[0]==1):
            mydict[combi]=np.floor(stocksSelect['hold'].iloc[0]/100)*100
            pass
        elif (stocksSelect.shape[0]==0):
            mydict[combi]=0
        else:
            print(f"{code} {combi} hold error!")
    t02=np.round(needtomove*t02ratio-mydict['T02'],-2)
    if (t02<0):
        t02=0
    t01=needtomove-t02

    for combi in combi312:
        hold312=mydict[combi]
        if (hold312>0):
            if (t02>0):
                if (hold312>t02):
                    #从该组合下部分划入t02
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':t02,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T02','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':t02,'name':myname,'price':price})
                    t02=0
                    mydict[combi]=hold312-t02
                else:
                    #从该组合下全部划入t02
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':hold312,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T02','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':hold312,'name':myname,'price':price})
                    t02=t02-hold312
                    mydict[combi]=0
    for combi in combi312:
        hold312=mydict[combi]
        if (hold312>0):
            if (t01>0):
                if (hold312>t01):
                    #从该组合下部分划入t01
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':t01,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T01','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':t01,'name':myname,'price':price})
                    t01=0
                    mydict[combi]=hold312-t01
                else:
                    #从该组合下全部划入t01
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':hold312,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T01','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':hold312,'name':myname,'price':price})
                    t01=t01-hold312
                    mydict[combi]=0
    pass
checkinfo=pd.DataFrame(data=checkinfo)
trytomove=pd.DataFrame(data=trytomove)
trytomove['市值']=trytomove['price']*trytomove['发生数量']
trytomove.to_excel('trytomove.xlsx')
print(trytomove)
#print(totalmovemv)
#print(checkinfo)
#movedf=checkinfo[['code','name','price','move','moveMarketValue']]
#print(movedf)
#movedf.to_csv('move20200415.xls')
#diff=checkinfo[abs(checkinfo['difference']*checkinfo['price'])>50000]
#diff['diffmv']=checkinfo['difference']*checkinfo['price']
#print(diff['diffmv'].sum())